package com.siemens.industrial.web.mvc.dao.partner;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created by shuan on 2017/1/19.
 */
@Service
public class PartnerPlanDowntimeDao {
    @Resource
    JdbcTemplate jdbcTemplate;
    private static final String databaseTag = "{database}";
    private static final String findDevicePlanDowntimes = "SELECT \n" +
            "    a.device_id,date_format(a.start_time,'%Y-%m-%d %H:%i:%s') as start_time,date_format(a.end_time,'%Y-%m-%d %H:%i:%s') as end_time,a.state,date_format(a.create_time,'%Y-%m-%d %H:%i:%s') as create_time, b.device_unique_id\n" +
            "FROM\n" +
            "    `"+ databaseTag +"`.device_state_period a\n" +
            "        LEFT JOIN\n" +
            "    `"+ databaseTag +"`.device b ON a.device_id = b.device_id\n" +
            "WHERE\n" +
            "    a.device_id = ?\n" +
            "        AND a.state = -1\n" +
            "ORDER BY start_time;";

    private static final String savePlanDowntime = "INSERT INTO `"+ databaseTag +"`.`device_state_period` (`device_id`, `start_time`,`end_time`,`state`) VALUES (?,?,?,-1);";

    public static final String deletePlanDowntime = "Delete FROM `"+ databaseTag +"`.`device_state_period` where device_id = ? and start_time = ? and end_time = ? and state = -1;";

    public static  final String findIntersections = "SELECT \n" +
            "    *\n" +
            "FROM\n" +
            "    `"+databaseTag+"`.device_state_period\n" +
            "WHERE\n" +
            "    device_id = ?\n" +
            "        AND state = - 1\n" +
            "        AND ((? <= start_time AND ? >= start_time)\n" +
            "        OR (? >= start_time AND ? <= end_time))\n" +
            "ORDER BY start_time;;";

    public List<Map<String,Object>> findDevicePlanDowntimes(String database, String deviceId){
        return jdbcTemplate.queryForList(findDevicePlanDowntimes.replace(databaseTag,database),deviceId);
    }

    public void savePlanDowntime(String database, String deviceId, Date startTime, Date endTime){
        jdbcTemplate.update(savePlanDowntime.replace(databaseTag,database),deviceId,startTime,endTime);
    }

    public int deletePlanDowntime(String database, String deviceId, String startTime, String endTime){
        return jdbcTemplate.update(deletePlanDowntime.replace(databaseTag,database),deviceId,startTime,endTime);
    }

    public List<Map<String,Object>> findIntersections(String database,String deviceId,String startTime,String endTime){
        return jdbcTemplate.queryForList(findIntersections.replace(databaseTag,database),deviceId,startTime,endTime,startTime,startTime);
    }
}
